package service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import exceptions.ConnectionNotFoundException;
import exceptions.GenericSQLException;

import model.Teacher;

/**
 * Session Bean implementation class PresenceService
 */
@Stateless(mappedName = "presenceService")
@LocalBean
public class PresenceService {

    /**
     * Default constructor.
     */
    public PresenceService() {
    }

    public ArrayList<Teacher> loadPresence(boolean present) {

        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet result = null;

        try {

            ArrayList<Teacher> teachers = new ArrayList<Teacher>();

            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/HRODevicesPool");
            conn = ds.getConnection();

            String query = "select id, name, code from teacher where present = ?";
            preparedStatement = conn.prepareStatement(query);
            preparedStatement.setBoolean(1, present);
            result = preparedStatement.executeQuery();

            while (result.next()) {

                int id = result.getInt("id");
                String name = result.getString("name");
                String code = result.getString("code");

                Teacher teacher = new Teacher(id, name, code);
                teachers.add(teacher);
            }

            return teachers;
        }
        catch (NamingException e) {

            e.printStackTrace();

            throw new ConnectionNotFoundException("De databaseverbinding kan niet worden opgevraagd");
        }
        catch (SQLException e) {

            e.printStackTrace();

            throw new GenericSQLException("Er gaat iets mis met opvragen van de teachers uit de database.");
        }
        finally {

            try {

                if (result != null) {
                    result.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public void setPresence(int teacherId, boolean present) {

        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet result = null;

        try {

            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/HRODevicesPool");
            conn = ds.getConnection();

            String query = "update teacher set present = ? where id = ?";
            preparedStatement = conn.prepareStatement(query);
            preparedStatement.setBoolean(1, present);
            preparedStatement.setInt(2, teacherId);
            int affectedRows = preparedStatement.executeUpdate();

            if (affectedRows != 1) {
                throw new GenericSQLException("Er gaat iets mis met het bijwerken van de status van teacher: " + teacherId);
            }
        }
        catch (NamingException e) {

            e.printStackTrace();

            throw new ConnectionNotFoundException("De databaseverbinding kan niet worden opgevraagd");
        }
        catch (SQLException e) {

            e.printStackTrace();

            throw new GenericSQLException("Er gaat iets mis met het bijwerken van de status van teacher: " + teacherId);
        }
        finally {

            try {

                if (result != null) {
                    result.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
